package com.mmy.dao.scenic;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.scenic.VScenicOrder;

public interface VScenicOrderRepository extends JpaRepository<VScenicOrder, Integer> {

	/*
	 * 根据会计对账状态查找
	 */
	@Query(value = "select *  from vScenicOrder where iAccountState = :strAccountState", nativeQuery = true)
	List<VScenicOrder> findByAccount(@Param("strAccountState") String strAccountState);

	/*
	 * 根据出纳审核状态查找
	 */
	@Query(value = "select *  from vScenicOrder where iCashierState = :strCashierState and left (cOrderID,6) = :iScenicID", nativeQuery = true)
	List<VScenicOrder> findByCashierAndScenicID(@Param("strCashierState") String strCashierState,
			@Param("iScenicID") int iScenicID);

	/*
	 * 根据会计审核状态查找
	 */
	@Query(value = "select *  from vScenicOrder where iAccountState = :strAccountState and left (cOrderID,6) = :iScenicID", nativeQuery = true)
	List<VScenicOrder> findByAccountAndScenicID(@Param("strAccountState") String strAccountState,
			@Param("iScenicID") int iScenicID);

	/**
	 * 通过iPlanID查询订单
	 *
	 * @author 创建人shangzl 修改人 hanxl
	 * @date 创建日期 修改日期 2021.01.22
	 */
	@Query(value = "select *  from vScenicOrder where  iPlanID= :iPlanID", nativeQuery = true)
	List<VScenicOrder> findByPlanID(@Param("iPlanID") int iPlanID);

	/**
	 * 个人中心通过登录人获取景点门票预留
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from vScenicOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc limit ?2,?3", nativeQuery = true)
	List<VScenicOrder> findByUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取景点门票订单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from vScenicOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<VScenicOrder> findByOrderUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取景点门票退单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from vScenicOrder where iOrderUserID=?1 and iOrderState=4 order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<VScenicOrder> findByChargeBackUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取景点门票预留 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from vScenicOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc", nativeQuery = true)
	int countByUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取景点门票退单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc", nativeQuery = true)
	int countByRefundUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取景点门票订单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc", nativeQuery = true)
	int countByOrderUserID(int iUserID);

	/**
	 * 后台 非管理员登录 查看景区订单时用到
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.29
	 */
	@Query(value = "select * from vScenicOrder where  iOrderState in (:lstOrderState) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) "
			+ "order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySearchOperator(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState,
			@Param("iOrgID") int iOrgID, @Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize);

	/**
	 * 后台非超级管理员登录 查看景区订单时用到 分页
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.29
	 */
	@Query(value = "select count(*)  from vScenicOrder  where  iOrderState in (:lstOrderState) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ "order by dOrderDate desc", nativeQuery = true)
	int countBySearchOperator(@Param("strScenicName") String strScenicName, @Param("strOrderName") String strOrderName,
			@Param("strOrderTel") String strOrderTel, @Param("strOrderDate") String strOrderDate,
			@Param("lstOrderState") List<Integer> lstOrderState, @Param("iOrgID") int iOrgID);

	/**
	 * 通过景区ID查询 查看景区订单时用到 未入园
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.29
	 */
	@Query(value = "select * from vScenicOrder where  iOrderState in(1,2) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ " and DateDIFF(dUseDate,now())>=0  and (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySearchOperatorUnUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iOrgID") int iOrgID, @Param("strOrderDate") String strOrderDate,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize);

	/**
	 * 通过景区ID查询 查看景区订单时用到 分页 未入园
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.29
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState in(1,2) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ "and DateDIFF(dUseDate,now())>=0 and  (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%)  order by dOrderDate desc", nativeQuery = true)
	int countBySearchOperatorUnUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iOrgID") int iOrgID, @Param("strOrderDate") String strOrderDate);

	/**
	 * 后台超级管理员 查看景区订单时用到 近期待使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select * from vScenicOrder where iOrderState in(1,2) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and DateDIFF(dUseDate,now()) BETWEEN 0 and :iMaxDay  and (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySeachAdministratorRecentlyUse(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iMaxDay") int iMaxDay, @Param("strOrderDate") String strOrderDate,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize);

	/**
	 * 后台超级管理员 查看景区订单时用到 分页 近期待使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState in(1,2) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and DateDIFF(dUseDate,now()) BETWEEN 0 and :iMaxDay  and (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc", nativeQuery = true)
	int countBySeachAdministratorRecentlyUse(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iMaxDay") int iMaxDay, @Param("strOrderDate") String strOrderDate);

	/**
	 * 通过景区ID查询 查看景区订单时用到 近期待使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.29
	 */
	@Query(value = "select * from vScenicOrder where  iOrderState in(1,2) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ " and DateDIFF(dUseDate,now()) BETWEEN 0 and :iMaxDay  and (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySearchOperatorRecentlyUse(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iOrgID") int iOrgID, @Param("iMaxDay") int iMaxDay, @Param("strOrderDate") String strOrderDate,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize);

	/**
	 * 通过景区ID查询 查看景区订单时用到 分页近期待使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.29
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState in(1,2) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ "and DateDIFF(dUseDate,now()) BETWEEN 0 and :iMaxDay and  (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc", nativeQuery = true)
	int countBySearchOperatorRecentlyUse(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iOrgID") int iOrgID, @Param("iMaxDay") int iMaxDay, @Param("strOrderDate") String strOrderDate);

	/**
	 * 通过景区ID查询 查看景区订单时用到
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.22
	 */
	@Query(value = "select * from vScenicOrder where  iScenicID=:iScenicID ", nativeQuery = true)
	List<VScenicOrder> getByScenicID(@Param("iScenicID") int iScenicID);

	/**
	 * 后台超级管理员 查看景区订单时用到
	 *
	 * @author 创建人 hanxl
	 * @param date
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select * from vScenicOrder where iOrderState in(:lstOrderState) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySeachAdministrator(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize);

	/**
	 * 后台超级管理员 查看景区订单时用到 分页
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState in(:lstOrderState) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc", nativeQuery = true)
	int countBySeachAdministrator(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("lstOrderState") List<Integer> lstOrderState);

	/**
	 * 后台超级管理员 查看景区订单时用到 未使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select * from vScenicOrder where iOrderState in(1,2) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and DateDIFF(dUseDate,now())>=0  and (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%)  order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySeachAdministratorUnUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize);

	/**
	 * 后台超级管理员 查看景区订单时用到 分页 未使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState in(1,2) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and DateDIFF(dUseDate,now())>=0  and (cSpare2='0' or cSpare2 is Null) "
			+ "and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc", nativeQuery = true)
	int countBySeachAdministratorUnUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate);

	/**
	 * 后台超级管理员 查看景区订单时用到 已使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */

	@Query(value = "select * from vScenicOrder where iOrderState =2 "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ " and cSpare2='1' and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySeachAdministratorUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize);

	/**
	 * 后台超级管理员 查看景区订单时用到 分页 已使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.25
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState =2 "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ " and cSpare2='1' and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc", nativeQuery = true)
	int countBySeachAdministratorUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("strOrderDate") String strOrderDate);

	/**
	 * 后台 非管理员操作 查看景区订单时用到 已使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.27
	 */
	@Query(value = "select * from vScenicOrder where  iOrderState =2 "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ " and cSpare2='1' and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%)  order by dOrderDate desc limit :iTempPageNo,:iPageSize ", nativeQuery = true)
	List<VScenicOrder> findBySearchOperatorUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iOrgID") int iOrgID, @Param("strOrderDate") String strOrderDate,
			@Param("iTempPageNo") int iTempPageNo, @Param("iPageSize") int iPageSize);

	/**
	 * 后台非超级管理员查询 查看景区订单时用到 分页已使用
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.27
	 */
	@Query(value = "select count(*)  from vScenicOrder where iOrderState =2 "
			+ "and  (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strOrderName = \"\" or cOrderName like %:strOrderName%) "
			+ "and  (:strOrderTel = \"\" or cOrderTel like %:strOrderTel%) "
			+ "and (cScenicOrgID=:iOrgID or (iOrderUserOrgID=:iOrgID and cScenicOrgID<>:iOrgID)) "
			+ " and cSpare2='1' and  (:strOrderDate = \"\" or dOrderDate like %:strOrderDate%) order by dOrderDate desc", nativeQuery = true)
	int countBySearchOperatorUsed(@Param("strScenicName") String strScenicName,
			@Param("strOrderName") String strOrderName, @Param("strOrderTel") String strOrderTel,
			@Param("iOrgID") int iOrgID, @Param("strOrderDate") String strOrderDate);

	/**
	 * 函数功能说明：后台显示订单详情
	 *
	 * @param 参数说明 vScenicOrder
	 * @return 返回值说明 jsp页面
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.28
	 */
	@Query(value = "select *  from vScenicOrder where iID=:iID", nativeQuery = true)
	VScenicOrder findByID(@Param("iID") int iID);

}
